from Education_AMS.models import *
from Education_AMS.view_models import *
from django.core import serializers
from django.db import connection
import json

cursor = connection.cursor()


# 学生登录
def login_student(username, password):
    sql = 'select check_stu_login(\'%s\',\'%s\') from dual' % (username, password)
    result = int(cursor.execute(sql).fetchone()[0])
    if result == 1:
        # 登录成功
        # 获取学生视图
        stu_list = json.loads(serializers.serialize("json", StudentInfo.objects.filter(sno=username)))
        return 1003, stu_list[0]['fields']
    stu_list = Student.objects.filter(sno=username)
    if result == -1:
        # 用户不存在
        return 1001, None
    else:
        # 密码错误
        return 1002, None


# 老师登录
def login_teacher(username, password):
    sql = 'select check_tea_login(\'%s\',\'%s\') from dual' % (username, password)
    result = int(cursor.execute(sql).fetchone()[0])
    if result == 1:
        # 登录成功
        # 获取老师视图
        tea_list = json.loads(serializers.serialize("json", TeacherInfo.objects.filter(tno=username)))
        return 1003, tea_list[0]['fields']
    stu_list = Student.objects.filter(sno=username)
    if result == -1:
        # 用户不存在
        return 1001, None
    else:
        return 1002, None


# 课程表
def get_course_table(username):
    ct = json.loads(serializers.serialize("json", SelectCourseInfo.objects.filter(sno=username)))
    course_table = [i['fields'] for i in ct]
    return course_table


# 成绩信息
def get_score(usernmae):
    sc = json.loads(serializers.serialize("json", GradeInfo.objects.filter(sno=usernmae)))
    score_list = [i['fields'] for i in sc]
    return score_list


# 得到单科绩点
def get_GPA1(score):
    if score >= 90:
        return 4.0
    if score >= 85:
        return 3.7
    if score >= 82:
        return 3.3
    if score >= 78:
        return 3.0
    if score >= 75:
        return 2.7
    if score >= 71:
        return 2.3
    if score >= 66:
        return 2.0
    if score >= 62:
        return 1.7
    if score >= 60:
        return 1.3
    return 0.0


# 得到平均绩点
def get_GPA(score, credit):
    if credit == 0:
        return 0.0
    return round(score / credit, 3)


# 切割课程节次信息
def split_course_time(course_time):
    sec = course_time.split(';')
    sections = []
    for i in sec:
        temp_sec = {}
        temp_sec['week'] = int(i.split(':')[0])
        temp_sec['sec'] = []
        temp_str = i.split(':')[1]
        for j in range(len(temp_str) // 2):
            temp_sec['sec'].append(int(temp_str[j * 2:j * 2 + 2]))
        sections.append(temp_sec)
    return sections


# 获取选修课信息
def get_select_course_info():
    sc = json.loads(serializers.serialize("json", CourseInfo.objects.filter(courseattribute='选修')))
    course_list = [i['fields'] for i in sc]
    index = 0
    for it in sc:
        course_list[index]['cno'] = it['pk']
        index += 1
    return course_list


def choose_course(username, cno, tno):
    selected = json.loads(serializers.serialize("json", SelectCourseInfo.objects.filter(sno=username)))
    course_time_list = [it2 for it in selected for it2 in it['fields']['course_time'].split(';')]
    cno_time = json.loads(serializers.serialize("json", CourseInfo.objects.filter(cno=cno)))[0]['fields']['course_time']
    if cno_time in course_time_list:
        # 课程时间冲突
        return 5
    res = -1
    username, cno, tno, res = cursor.callproc('INSERT_SELECT_COURSE', [username, cno, tno, res])
    return res


def get_selected_course_info(username):
    sc = json.loads(serializers.serialize("json", SelectCourseInfo.objects.filter(courseattribute='选修', sno=username)))
    course_list = [i['fields'] for i in sc]
    return course_list


def exit_course(sno, cno):
    try:
        SelectCourse.objects.filter(sno=sno, cno=cno).delete()
    except BaseException:
        return '退课失败'
    return "退课成功"


def evaluate_teacher(sno, tno, level):
    res = -1
    sno, tno, level, res = cursor.callproc('insert_evaluation', [sno, tno, level, res])
    return res
